package dao;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class TrackDaoImpl implements TrackDao {
    private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";

    private static final String DATABASE_PROPERTIES_FILE = "jdbc.properties";
    private static final String URL_PROPERTY = "url";
    private static final String USERNAME_PROPERTY = "username";
    private static final String PASSWORD_PROPERTY = "password";

    private static final String SELECT_TRACKS_BY_ALBUM_ID_STATEMENT = "SELECT * FROM tracks WHERE album_id = ?;";

    private static final String TRACK_ID_COLUMN = "track_id";
    private static final String TRACK_NUMBER_COLUMN = "track_number";
    private static final String TRACK_NAME_COLUMN = "track_name";
    private static final String TRACK_LENGTH_COLUMN = "track_length";

    private static final TrackDao INSTANCE = new TrackDaoImpl();

    private static String url;
    private static String username;
    private static String password;

    static {
        readDatabaseProperties();
    }

    private TrackDaoImpl() {

    }

    private static void readDatabaseProperties() {
        InputStream in = null;

        try {
            Properties properties = new Properties();
            in = ArtistDaoImpl.class.getResourceAsStream(DATABASE_PROPERTIES_FILE);

            if (in != null) {
                properties.load(in);
            }

            String url = properties.getProperty(URL_PROPERTY);
            String username = properties.getProperty(USERNAME_PROPERTY);
            String password = properties.getProperty(PASSWORD_PROPERTY);

            TrackDaoImpl.url = url;
            TrackDaoImpl.username = username;
            TrackDaoImpl.password = password;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static TrackDao getInstance() {
        return INSTANCE;
    }

    public List<Track> getTracksByAlbumId(Long albumId) {
        List<Track> tracks = new ArrayList<Track>();

        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(SELECT_TRACKS_BY_ALBUM_ID_STATEMENT);
            preparedStatement.setLong(1, albumId);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Track track = mapResultSet(resultSet);
                tracks.add(track);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return tracks;
    }

    private Track mapResultSet(ResultSet resultSet) {
        Track track = new Track();

        try {
            track.setId(resultSet.getLong(TRACK_ID_COLUMN));
            track.setNumber(resultSet.getInt(TRACK_NUMBER_COLUMN));
            track.setName(resultSet.getString(TRACK_NAME_COLUMN));
            track.setLength(resultSet.getString(TRACK_LENGTH_COLUMN));
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return track;
    }

    private Connection getConnection() {
        Connection connection = null;

        try {
            Class.forName(DRIVER_CLASS);
            connection = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;
    }
}
